﻿// $Id: SqlServer.cs 66 2008-05-07 19:57:30Z nvivo $

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using f = System.Windows.Forms;

namespace DBLGen.Plugins.Import
{
    [Plugin("Microsoft SQL Server 2000/2005")]
    [PluginSettings(typeof(SqlServerSettings), typeof(SqlServerSettingsEditor))]
    public class SqlServer : BaseImportPlugin
    {

        #region Queries for SQL 2000 

        public static readonly string Query_Tables_80 = 
@"select
	user_name(uid) as tableschema,
	name as tablename, 
	case xtype
		when 'U' then 'Table'
		when 'V' then 'View'
	end as tabletype
from sysobjects
where xtype in ('U', 'V')
order by 1, 2";

        public static readonly string Query_Columns_80 =
@"
select tobj.id, c.name
	into #pks
from sysobjects kobj
	inner join sysobjects tobj on kobj.parent_obj = tobj.id
	inner join sysindexes i on i.id = kobj.parent_obj and i.name = kobj.name
	inner join sysindexkeys ik on ik.id = kobj.parent_obj and ik.indid = i.indid
	inner join syscolumns c on c.id = kobj.parent_obj and c.colid = ik.colid
where kobj.xtype = 'PK'

select o.id, c.name, user_name(ro.uid) as ref_tableschema, ro.name as ref_tablename, rc.name as ref_columnname
	into #fks
from sysforeignkeys fk
	inner join sysobjects o on fk.fkeyid = o.id
	inner join syscolumns c on fk.fkeyid = c.id and fk.fkey = c.colid
	inner join sysobjects ro on fk.rkeyid = ro.id
	inner join syscolumns rc on fk.rkeyid = rc.id and fk.rkey = rc.colid

select 
	user_name(o.uid) as tableschema,
	o.name as tablename,
	c.colorder as columnorder,
	c.name as columnname,
	t.name as type,
	case
		when t.collation is not null then c.prec
		else null
	end as length,
	c.length as octetlength,
	case
		when t.collation is null then t.prec
		else null
	end as [precision],
	case 
		when t.collation is null then t.scale
		else null
	end as scale,
	c.isnullable,
	c.iscomputed,
	case
		when pk.id is null then 0
		else 1
	end as ispk,
	case
		when fk.id is null then 0
		else 1
	end as isfk,
	fk.ref_tableschema,
	fk.ref_tablename,
	fk.ref_columnname
from sysobjects o
	inner join syscolumns c on o.id = c.id
	inner join systypes t on c.xusertype = t.xusertype
	left join #pks pk on o.id = pk.id and c.name = pk.name
	left join #fks fk on o.id = fk.id and c.name = fk.name
where o.xtype in ('U', 'V')

drop table #pks
drop table #fks
";

        #endregion

        #region Queries for SQL 2005 and later

        public static readonly string Query_Tables_90 =
@"select
	s.name as tableschema,
	o.name as tablename,
	case o.type
		when 'U' then 'Table'
		when 'V' then 'View'
	end as tabletype
from sys.schemas s
	inner join sys.objects o on s.schema_id = o.schema_id
where o.type in ('U', 'V')
order by 1, 2";

        private static readonly string Query_Columns_90 = @"
select o.object_id, c.name
	into #pks
from sys.key_constraints kc
	inner join sys.objects o on o.object_id = kc.parent_object_id
	inner join sys.schemas s on s.schema_id = o.schema_id
	inner join sys.index_columns ic on ic.object_id = kc.parent_object_id and ic.index_id = kc.unique_index_id
	inner join sys.columns c on c.object_id = kc.parent_object_id and c.column_id = ic.column_id
where kc.type = 'PK'

select o.object_id, c.name, rs.name as ref_tableschema, ro.name as ref_tablename, rc.name as ref_columnname
	into #fks
from sys.foreign_key_columns fk
	inner join sys.objects o on o.object_id = fk.parent_object_id
	inner join sys.schemas s on s.schema_id = o.schema_id
	inner join sys.columns c on c.object_id = fk.parent_object_id and c.column_id = fk.parent_column_id
	inner join sys.objects ro on ro.object_id = fk.referenced_object_id
	inner join sys.schemas rs on rs.schema_id = ro.schema_id
	inner join sys.columns rc on rc.object_id = fk.referenced_object_id and rc.column_id = fk.referenced_column_id

select
	s.name as tableschema,
	o.name as tablename,
	c.column_id as columnorder,
	c.name as columnname,
	t.name as type,
	case
		when c.max_length < 0 then c.max_length
		when st.name in ('nchar', 'nvarchar') then c.max_length / 2
		when st.name in ('char', 'varchar') then c.max_length
		else null
	end as length,
	case
		when st.name in ('text', 'ntext') then null
		else c.max_length
	end as octetlength,
	c.precision,
	c.scale,
	c.is_nullable as isnullable,
	c.is_computed as iscomputed,
	case
		when pk.object_id is null then 0
		else 1
	end as ispk,
	case
		when fk.object_id is null then 0
		else 1
	end as isfk,
	fk.ref_tableschema,
	fk.ref_tablename,
	fk.ref_columnname
from sys.schemas s
	inner join sys.objects o on s.schema_id = o.schema_id
	inner join sys.columns c on o.object_id = c.object_id
	inner join sys.types t on c.user_type_id = t.user_type_id
	inner join sys.types st on st.user_type_id = t.system_type_id
	left join #pks pk on c.object_id = pk.object_id and c.name = pk.name
	left join #fks fk on c.object_id = fk.object_id and c.name = fk.name
where o.type in ('U', 'V')
order by 1, 2, 3

drop table #pks
drop table #fks
";

        #endregion

        protected override DbConnection CreateConnection()
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = GetConnectionString();

            return connection;
        }

        public override Table[] GetAllTables()
        {
            DataTable tables = CreateTablesReferenceTable();
            DataTable columns = CreateColumnsReferenceTable();

            using (SqlConnection connection = (SqlConnection) CreateConnection())
            {
                connection.Open();
                
                Version v = new Version(connection.ServerVersion);

                if (v.Major >= 9)
                {
                    FillDataTable(tables, Query_Tables_90, connection);
                    FillDataTable(columns, Query_Columns_90, connection);
                }
                else
                {
                    FillDataTable(tables, Query_Tables_80, connection);
                    FillDataTable(columns, Query_Columns_80, connection);
                }

                connection.Close();
            }

            return BuildTables(tables, columns);
        }

        private string GetConnectionString()
        {
            SqlServerSettings settings = (SqlServerSettings) Settings;

            if (settings.UseWindowsAuthentication)
                return String.Format("Server={0}; Database={1}; Integrated Security=SSPI;", settings.Host, settings.Database);
            else
                return String.Format("Server={0}; Database={1}; User ID={2}; Password={3};", settings.Host, settings.Database, settings.User, settings.Password);
        }

        private static void FillDataTable(DataTable dataTable, string query, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);

            adapter.Fill(dataTable);
        }
    }
}
